DROP FUNCTION IF EXISTS FIRST_DAY(dt DATE);
CREATE OR REPLACE FIRST_DAY(dt DATE) RETURNS DATE AS $$
 DECLARE                                                       
   new_date date;                                              
 BEGIN                                                         
   select dt - date_part('day', dt)::INTEGER + 1 INTO new_date;
   RETURN new_date;                                            
 END;
 $$ LANGUAGE plpgsql;                                                         


DROP FUNCTION IF EXISTS MY_CPF_DIGITO_VERIFICADOR_VALIDO(cpf TEXT);
CREATE OR REPLACE FUNCTION MY_CPF_DIGITO_VERIFICADOR_VALIDO(cpf TEXT) RETURNS BOOLEAN AS $$
 DECLARE                                                               
 digit1 INTEGER;                                                       
   digit2 INTEGER;                                                     
 passed BOOLEAN;                                                       
 BEGIN                                                                 
 digit1 := (substring(cpf from '^(\d)')::INTEGER                       
   + 2 * substring(cpf from '^\d(\d)')::INTEGER                        
   + 3 * substring(cpf from '^\d{2}(\d)')::INTEGER                     
   + 4 * substring(cpf from '^\d{3}\.(\d)')::INTEGER                   
   + 5 * substring(cpf from '^\d{3}\.\d(\d)')::INTEGER                 
   + 6 * substring(cpf from '^\d{3}\.\d{2}(\d)')::INTEGER              
   + 7 * substring(cpf from '^\d{3}\.\d{3}\.(\d)')::INTEGER            
   + 8 * substring(cpf from '^\d{3}\.\d{3}\.\d(\d)')::INTEGER          
   + 9 * substring(cpf from '^\d{3}\.\d{3}\.\d{2}(\d)')::INTEGER) % 11;
                                                                       
 digit2 := (substring(cpf from '^\d(\d)')::INTEGER                     
   + 2 * substring(cpf from '^\d{2}(\d)')::INTEGER                     
   + 3 * substring(cpf from '^\d{3}\.(\d)')::INTEGER                   
   + 4 * substring(cpf from '^\d{3}\.\d(\d)')::INTEGER                 
   + 5 * substring(cpf from '^\d{3}\.\d{2}(\d)')::INTEGER              
   + 6 * substring(cpf from '^\d{3}\.\d{3}\.(\d)')::INTEGER            
   + 7 * substring(cpf from '^\d{3}\.\d{3}\.\d(\d)')::INTEGER          
   + 8 * substring(cpf from '^\d{3}\.\d{3}\.\d{2}(\d)')::INTEGER       
 + 9 * digit1) % 11;                                                   
                                                                       
 SELECT (                                                              
 digit1 = substring(cpf from '(\d)\d$')::INTEGER                       
 AND digit2 = substring(cpf from '(\d)$')::INTEGER                     
 ) INTO passed;                                                        
                                                                       
 RETURN passed;                                                        
 END;
 $$ LANGUAGE plpgsql;                                                                 

DROP DOMAIN IF EXISTS MY_CPF;
CREATE DOMAIN MY_CPF AS CHAR(14) 
  CONSTRAINT DOMAIN_MY_CPF_CHK_FORMAT CHECK(VALUE ~ '^\d{3}\.\d{3}\.\d{3}-\d{2}')
  CONSTRAINT DOMAIN_MY_CPF_CHK_DIGITO CHECK(MY_CPF_DIGITO_VERIFICADOR_VALIDO(VALUE));

DROP DOMAIN IF EXISTS DT_NASCIMENTO;
CREATE DOMAIN DT_NASCIMENTO AS DATE
	CONSTRAINT DOMAIN_DT_NASCIMENTO_CHK_is_consistent CHECK(VALUE < current_date - interval '5 years');


DROP DOMAIN IF EXISTS EMAIL;
CREATE DOMAIN EMAIL AS VARCHAR(60)
  CONSTRAINT DOMAIN_EMAIL_CHK_FORMAT CHECK(VALUE ~ '^.+\@.+\..+$');


DROP DOMAIN IF EXISTS ESTADO_CIVIL;
CREATE DOMAIN ESTADO_CIVIL AS CHAR(10)
  CONSTRAINT DOMAIN_ESTADO_CIVIL_CHK_options CHECK(VALUE IN ('Solteiro', 'Casado', 'Divorciado', 'Viúvo'));

DROP DOMAIN IF EXISTS FASE;
CREATE DOMAIN FASE AS CHAR(15)
  CONSTRAINT DOMAIN_FASE_CHK_OPTIONS CHECK(VALUE in ('Em Análise', 'Selecionado', 'Não-selecionado'));


DROP TABLE IF EXISTS AssistenteSocial;
CREATE TABLE AssistenteSocial(
  cpf                      MY_CPF                           NOT NULL,
  nome                     VARCHAR(255)                     NOT NULL,
  data_nascimento          DT_NASCIMENTO                    NOT NULL,
  email                    EMAIL               UNIQUE       NOT NULL,
  senha                    VARCHAR(255)                     NOT NULL,

  CONSTRAINT AssistenteSocial_PK PRIMARY KEY(cpf)
);
-- Constraints
-- cpf: PK
-- cpf: <constraints MY_CPF>
-- email: .*@.*\..*
-- data_nascimento < now - 5 anos


DROP TABLE IF EXISTS Adolescente;
CREATE TABLE Adolescente(
  id_adolescente            INTEGER                         NOT NULL,
  tipo_adolescente          CHAR(11)                        NOT NULL,
  nome                      VARCHAR(255)                    NOT NULL,
  data_nascimento           DT_NASCIMENTO                   NOT NULL,
  end_logradouro            VARCHAR(255)                    NOT NULL,
  end_numero                INTEGER                         NOT NULL,
  end_bairro                VARCHAR(255)                    NOT NULL,
  end_cidade                VARCHAR(255)                    NOT NULL,
  end_estado                VARCHAR(255)                    NOT NULL,
  end_pais                  VARCHAR(255)                    NOT NULL,
  end_ponto_referencia      VARCHAR(255)                        NULL,
  end_cep                   CHAR(9)                         NOT NULL,
  tec_serie                 NUMERIC(2,0)                        NULL, -- dois digitos no máximo: 99.0
  tec_nome                  VARCHAR(255)                        NULL,

  CONSTRAINT Adolescente_PK PRIMARY KEY(id_adolescente),
  CONSTRAINT Adolescente_CHK_tipo_adolescente_options CHECK(tipo_adolescente IN ('PreInscrito', 'Inscrito', 'Bolsista')),
  CONSTRAINT Adolescente_CHK_end_cep_format CHECK(end_cep ~ '^\d{5}\-\d{3}$'),
  CONSTRAINT Adolescente_CHK_tec_dependence CHECK(
    (tec_serie is NULL AND tec_nome is NULL)
    OR (tec_serie is not NULL AND tec_nome is not NULL))
);
-- Constraints
-- id_adolescente: PK
-- tipo_adolescente: ['PreInscrito', 'Inscrito', 'Bolsista']
-- end_cep: \d{5}-\d{3}
-- data_nascimento < now - 5 anos
-- E tec_nome => E tec_serie
-- E tec_serie => E tec_nome

DROP TABLE IF EXISTS Candidato;
CREATE TABLE Candidato(
  id_adolescente            INTEGER                   NOT NULL,
  numero_de_inscricao       INTEGER      UNIQUE       NOT NULL,

  CONSTRAINT Candidato_PK PRIMARY KEY(id_adolescente),
  CONSTRAINT Candidato_FK_Adolescente FOREIGN KEY(id_adolescente) REFERENCES Adolescente(id_adolescente)
);
-- Constraints
-- id_adolescente: PK
-- id_adolescente: FK -> Adolescente

DROP TABLE IF EXISTS Participante;
CREATE TABLE Participante(
  id_adolescente                INTEGER                      NOT NULL,
  foto                          BYTEA                            NULL, -- pode ser NULL enquanto o upload não ficar pronto
  naturalidade                  VARCHAR(150)                 NOT NULL,
  estado_civil                  ESTADO_CIVIL                 NOT NULL,
  facebook                      VARCHAR(150)                 NOT NULL,
  email                         VARCHAR(150)      UNIQUE     NOT NULL,
  observacao_sobre_familiares   TEXT                         NOT NULL,
  moradia_tipo                  CHAR(22)                     NOT NULL,
  moradia_valor                 NUMERIC(4,2)                     NULL, -- No máximo 4 digitos mais as 2 casas decimais: 9999.99
  bolsa_instituicao_de_ensino   NUMERIC(2,0)                     NULL, -- No máximo 2 digitos sem casa decimal: 99.0
  beneficio_governamental       CHAR(13)                         NULL,
  transporte_ida                TIME WITH TIME ZONE          NOT NULL,
  transporte_volta              TIME WITH TIME ZONE          NOT NULL,
  transporte_valor_diario       NUMERIC(2,2)                 NOT NULL, -- No máximo 2 digitos mais as 2 casas decimais: 99.99
  refeicao_local                CHAR(30)                     NOT NULL,
  refeicao_outro                VARCHAR(255)                     NULL,

  CONSTRAINT Participante_PK PRIMARY KEY(id_adolescente),
  CONSTRAINT Participante_FK_Adolescente FOREIGN KEY(id_adolescente) REFERENCES Adolescente(id_adolescente),
  CONSTRAINT Participante_CHK_moradia_tipo_options
    CHECK(moradia_tipo IN ('Alugado', 'Cedida permanentemente', 'Cedida temporariamente', 'Financiada')),
  CONSTRAINT Participante_CHK_beneficio_governamental_options
    CHECK(beneficio_governamental IN ('Bolsa família', 'Bolsa carioca')),
  CONSTRAINT Participante_CHK_refeicao_local_options
    CHECK(refeicao_local IN ('Trailers', 'Restaurantes', 'Cantina ou Restaurante escolar', 'Casa dos pais', 'Outro')),
  CONSTRAINT Participante_CHK_refeicao_outro CHECK(
    (refeicao_local = 'Outro' AND refeicao_outro is not NULL)
    OR (refeicao_local != 'Outro' AND refeicao_outro is NULL)
  )
);
-- Constraints
-- id_adolescente: PK
-- id_adolescente: FK -> Adolescente
-- estado_civil: ['Solteiro', 'Casado', 'Divorciado' ou 'Viúvo']
-- moradia_tipo: ['Alugado', 'Cedida permanentemente', 'Cedida temporariamente', 'Financiada']
-- beneficio_governamental: ['Bolsa família' e 'Bolsa carioca']
-- refeicao_local: ['Trailers', 'Restaurantes', 'Cantina ou Restaurante escolar', 'Casa dos pais', 'Outro']
-- refeicao_local = 'Outro' => refeicao_outro é obrigatório

DROP TABLE IF EXISTS PreInscrito;
CREATE TABLE PreInscrito(
  id_adolescente                    INTEGER                     NOT NULL,
  indicacao                         VARCHAR(255)                    NULL,
  fase                              FASE                        NOT NULL   DEFAULT 'Em Análise',
  data_inscricao                    TIMESTAMP WITH TIME ZONE    NOT NULL,
  cpf_assistente_pre_inscreveu      MY_CPF                      NOT NULL,
  entrevista_data_ligacao           TIMESTAMP WITH TIME ZONE        NULL,
  entrevista_data_marcada           TIMESTAMP WITH TIME ZONE        NULL,
  cpf_assistente_marcou_entrevista  MY_CPF                          NULL,

  CONSTRAINT PreInscrito_PK PRIMARY KEY(id_adolescente),
  CONSTRAINT PreInscrito_FK_Candidato FOREIGN KEY(id_adolescente) REFERENCES Candidato(id_adolescente),
  CONSTRAINT PreInscrito_FK_Adolescente FOREIGN KEY(id_adolescente) REFERENCES Adolescente(id_adolescente),
  CONSTRAINT PreInscrito_FK_Assistente_Pre_Inscreveu
    FOREIGN KEY(cpf_assistente_pre_inscreveu) REFERENCES AssistenteSocial(cpf),
  CONSTRAINT PreInscrito_FK_Assistente_Marcou_Entrevista
    FOREIGN KEY(cpf_assistente_marcou_entrevista) REFERENCES AssistenteSocial(cpf),
  CONSTRAINT PreInscrito_CHK_data_inscricao_consistent
    CHECK(data_inscricao <= current_timestamp),
  CONSTRAINT PreInscrito_CHK_entrevista_data_ligacao_consistent
    CHECK(entrevista_data_ligacao < current_timestamp),
  CONSTRAINT PreInscrito_CHK_entrevista_data_marcada_consistent
    CHECK(entrevista_data_marcada > current_timestamp)
);

CREATE INDEX PreInscrito_INX_FK_Assistente_Pre_Inscreveu
  ON PreInscrito(cpf_assistente_pre_inscreveu);

CREATE INDEX PreInscrito_INX_FK_Assistente_Marcou_Entrevista
  ON PreInscrito(cpf_assistente_marcou_entrevista);

-- Constraints
-- id_adolescente: PK
-- id_adolescente: FK => Candidato
-- id_adolescente: FK => Adolescente
-- id_adolescente: Adolescente relacionado deve ser do tipo 'PreInscrito'
-- cpf_assistente_pre_inscreveu: FK => Assistente
-- cpf_assistente_marcou_entrevista: FK => Assistente
-- cpf_assistente_pre_inscreveu: <cpf constraints>
-- cpf_assistente_marcou_entrevista: <cpf constraints>
-- fase: ['Em Análise', 'Selecionado', 'Não-selecionado']
-- data_inscricao: <= now
-- entrevista_data_ligacao < now
-- entrevista_data_marcada > now

DROP TABLE IF EXISTS Inscrito;
CREATE TABLE Inscrito(
  id_adolescente               INTEGER                            NOT NULL,
  cpf                          MY_CPF                                 NULL,
  rg                           CHAR(20)                               NULL,
  fase                         FASE                               NOT NULL   DEFAULT 'Em Análise',
  data_inscricao               TIMESTAMP WITH TIME ZONE           NOT NULL,
  cpf_assistente_inscreveu     MY_CPF                             NOT NULL,
  entrevista_data              TIMESTAMP WITH TIME ZONE               NULL,
  entrevista_observacoes       TEXT                                   NULL,
  cpf_assistente_entrevistou   MY_CPF                                 NULL,

  CONSTRAINT Inscrito_PK PRIMARY KEY(id_adolescente),
  CONSTRAINT Inscrito_FK_Candidato FOREIGN KEY(id_adolescente) REFERENCES Candidato(id_adolescente),
  CONSTRAINT Inscrito_FK_Participante FOREIGN KEY(id_adolescente) REFERENCES Participante(id_adolescente),
  CONSTRAINT Inscrito_FK_Adolescente FOREIGN KEY(id_adolescente) REFERENCES Adolescente(id_adolescente),
  CONSTRAINT Inscrito_FK_Assistente_Inscreveu FOREIGN KEY(cpf_assistente_inscreveu) REFERENCES AssistenteSocial(cpf),
  CONSTRAINT Inscrito_FK_Assistente_Entrevistou FOREIGN KEY(cpf_assistente_entrevistou) REFERENCES AssistenteSocial(cpf),
  CONSTRAINT Inscrito_CHK_data_inscricao_consistent CHECK(data_inscricao <= current_timestamp),
  CONSTRAINT Inscrito_CHK_entrevista_data CHECK(entrevista_data < current_timestamp)
);

CREATE INDEX Inscrito_INX_FK_Assistente_Inscreveu
  ON Inscrito(cpf_assistente_inscreveu);

CREATE INDEX Inscrito_INX_FK_Assistente_Entrevistou
  ON Inscrito(cpf_assistente_entrevistou);

-- Constraints
-- id_adolescente: PK
-- id_adolescente: FK => Candidato
-- id_adolescente: FK => Participante
-- id_adolescente: FK => Adolescente
-- id_adolescente: Adolescente relacionado deve ser do tipo 'Inscrito'
-- cpf_assistente_inscreveu: FK => Assistente
-- cpf_assistente_entrevistou: FK => Assistente
-- cpf: <constraints MY_CPF>
-- cpf_assistente_inscreveu: <constraints MY_CPF>
-- cpf_assistente_entrevistou: <constraints MY_CPF>
-- fase: ['Em Análise', 'Selecionado', 'Não-selecionado']
-- data_inscricao: <= now
-- entrevista_data: < now

DROP TABLE IF EXISTS Bolsista;
CREATE TABLE Bolsista(
  id_adolescente                INTEGER                                NOT NULL,
  numero_de_pasta               INTEGER                    UNIQUE      NOT NULL,
  cpf                           MY_CPF                     UNIQUE      NOT NULL,
  rg                            CHAR(20)                   UNIQUE      NOT NULL,
  data_de_selecao               TIMESTAMP WITH TIME ZONE               NOT NULL,
  data_da_primeira_entrevista   TIMESTAMP WITH TIME ZONE               NOT NULL,
  cpf_assistente_selecionou     MY_CPF                                 NOT NULL,

  CONSTRAINT Bolsista_PK PRIMARY KEY(id_adolescente),
  CONSTRAINT Bolsista_FK_Participante FOREIGN KEY(id_adolescente) REFERENCES Participante(id_adolescente),
  CONSTRAINT Bolsista_FK_Adolescente FOREIGN KEY(id_adolescente) REFERENCES Adolescente(id_adolescente),
  CONSTRAINT Bolsista_FK_Assistente_Selecionou FOREIGN KEY(cpf_assistente_selecionou) REFERENCES AssistenteSocial(cpf),
  CONSTRAINT Bolsista_CHK_data_selecao_consistent CHECK(data_de_selecao < current_timestamp),
  CONSTRAINT Bolsista_CHK_data_primeira_entrevista_consistent CHECK(data_da_primeira_entrevista > current_timestamp)
);

CREATE INDEX Bolsista_INX_FK_Assistente_Selecionou
  ON Bolsista(cpf_assistente_selecionou);

-- Constraints
-- id_adolescente: PK
-- id_adolescente: FK => Participante
-- id_adolescente: FK => Adolescente
-- cpf_assistente_selecionou: FK => Assistente
-- cpf: <constraints MY_CPF>
-- cpf_assistente_selecionou: <constraints MY_CPF>
-- data_de_selecao: < now
-- data_da_primeira_entrevista: > now

DROP TABLE IF EXISTS Escolaridade;
CREATE TABLE Escolaridade(
  id_adolescente         INTEGER                                  NOT NULL,
  instituicao_de_ensino  VARCHAR(255)                             NOT NULL,
  turno                  CHAR(10)                                 NOT NULL,
  ano_de_ingresso        NUMERIC(4,0)                                 NULL, -- Máximo de 4 digitos sem casa decimal
  termino_previsto       DATE                                         NULL,
  nivel_de_ensino        CHAR(11)                                     NULL,
  serie                  NUMERIC(2,0)                                 NULL, -- Máximo de 2 digitos sem casa decimal
  periodo                NUMERIC(2,0)                                 NULL, -- Máximo de 2 digitos sem casa decimal
  curso                  VARCHAR(255)                                 NULL,

  CONSTRAINT Escolaridade_PK PRIMARY KEY (id_adolescente),
  CONSTRAINT Escolaridade_FK_Adolescente FOREIGN KEY(id_adolescente) REFERENCES Adolescente(id_adolescente),
  CONSTRAINT Escolaridade_CHK_turno_options CHECK(turno in ('Matutino', 'Vespertino', 'Noturno')),
  CONSTRAINT Escolaridade_CHK_ano_ingresso_consistent CHECK(ano_de_ingresso < date_part('year', current_date)),
  CONSTRAINT Escolaridade_CHK_termino_previsto_consistent CHECK(termino_previsto >= first_day(current_date)),
  CONSTRAINT Escolaridade_CHK_termino_previsto_is_first_day CHECK(date_part('day', termino_previsto) = 1),
  CONSTRAINT Escolaridade_CHK_nivel_ensino_options CHECK(nivel_de_ensino IN ('Fundamental', 'Médio')),
  CONSTRAINT Escolaridade_CHK_basico_superior_exclusive CHECK(
    (nivel_de_ensino is not NULL AND serie is not NULL AND periodo is NULL AND curso is NULL)
    OR (nivel_de_ensino is NULL AND serie is NULL AND periodo is not NULL AND curso is not NULL)
  )
);

CREATE INDEX Escolaridade_INX_FK_Adolescente
  ON Escolaridade(id_adolescente);

-- Constraints
-- instituicao_de_ensino: PK
-- id_adolescente: FK => Adolescente
-- turno: ['Matutino', 'Vespertino', 'Noturno']
-- ano_de_ingresso: < ano atual
-- termino_previsto: >= mes/ano atual
-- termino_previsto: dia = 1
-- nivel_de_ensino: ['Fundamental' e 'Médio']
-- E nivel_de_ensino => E serie
-- E serie => E nivel_de_ensino
-- E serie || nivel_de_ensino => !E periodo e !E curso
-- E periodo => E curso
-- E curso => E periodo
-- E curso || periodo => !E nivel_de_ensino e !E serie



DROP TABLE IF EXISTS Familiar;
CREATE TABLE Familiar (
  id_familiar          SERIAL          NOT NULL,
  nome                 VARCHAR(255)    NOT NULL,
  data_nascimento      DT_NASCIMENTO   NOT NULL,
  grau_de_parentesco   CHAR(10)        NOT NULL,
  estado_civil         ESTADO_CIVIL    NOT NULL,
  profissao            VARCHAR(150)    NOT NULL,
  escolaridade         CHAR(29)        NOT NULL,
  renda_mensal         NUMERIC(8, 2)   NOT NULL, -- Máximo de '100000.00' por mês
  id_participante      INTEGER         NOT NULL,

  CONSTRAINT Familiar_PK PRIMARY KEY(id_familiar),
  CONSTRAINT Familiar_FK_Participante FOREIGN KEY(id_participante) REFERENCES Participante(id_adolescente),
  CONSTRAINT Familiar_CHK_grau_de_parentesco_options CHECK(grau_de_parentesco IN ('Pai', 'Mãe', 'Primo', 'Irmão', 'Tio', 'Avô', 'Filho', 'Sobrinho', 'Sogro', 'Genro', 'Padastro', 'Madastra', 'Cunhado', 'Concunhado', 'meio-irmão')),
  CONSTRAINT Familiar_CHK_escolaridade_options CHECK(escolaridade IN ('Ensino fundamental completo', 'Ensino fundamental incompleto', 'Ensino médio completo', 'Ensino médio incompleto', 'Ensino superior incompleto', 'Ensino superior completo'))
);
CREATE INDEX Familiar_INX_FK_Participante
  ON Familiar(id_participante);

-- Constraints
-- id_familiar PK
-- id_participante FK -> Participante
-- grau_de_parentesco: ['Pai', 'Mãe', 'Primo', 'Irmão', 'Tio', 'Avô', 'Filho', 'Sobrinho', 'Sogro', 'Genro', 'Padastro', 'Madastra', 'Cunhado', 'Concunhado' ou 'meio-irmão']
-- estado_civil: ['Solteiro', 'Casado', 'Divorciado' ou 'Viúvo']
-- escolaridade: [ 'Ensino Fundamental completo', 'Ensino Fundamental incompleto', 'Ensino Médio completo', 'Ensino Médio incompleto', 'Ensino Superior incompleto' ou 'Ensino superior completo']
-- data_nascimento < now - 5 anos


DROP TABLE IF EXISTS Genitor;
CREATE TABLE Genitor(
  cpf                      MY_CPF                                      NOT NULL,
  rg                       CHAR(20)               UNIQUE               NOT NULL,
  nome                     VARCHAR(255)                                NOT NULL,
  data_nascimento          DT_NASCIMENTO                               NOT NULL,
  tipo                     CHAR(3)                                     NOT NULL,
  profissao                VARCHAR(150)                                NOT NULL,
  naturalidade             VARCHAR(150)                                NOT NULL,
  ano_de_falecimento       SMALLINT                                    NULL,
  id_participante          INTEGER                                     NOT NULL,

  CONSTRAINT Genitor_PK PRIMARY KEY(cpf),
  CONSTRAINT Genitor_FK_Participante FOREIGN KEY(id_participante) REFERENCES Participante(id_adolescente),
  CONSTRAINT Genitor_CHK_tipo_options CHECK(tipo in ('Pai', 'Mãe')),
  CONSTRAINT Genitor_CHK_ano_falecimento_consistent CHECK(ano_de_falecimento <= (date_part('year', current_date)))
);
CREATE INDEX Genitor_INX_FK_Participante
  ON Genitor(id_participante);

-- Constraints
-- cpf: PK
-- cpf: \d{3}\.\d{3}.\d{3}-\d{2}
-- cpf: digito verificador
-- id_participante FK -> Participante
-- tipo: ['Pai', 'Mãe']
-- ano_de_falecimento <= ano atual
-- data_nascimento < now - 5 anos



DROP TABLE IF EXISTS Documento;
CREATE TABLE Documento(
  id_documento              SERIAL                                NOT NULL,
  nome                      VARCHAR(255)                          NOT NULL,
  pdf                       BYTEA                                 NOT NULL,
  data_entrega              DATE                                  NOT NULL,
  id_participante           INTEGER                               NOT NULL,
  cpf_assistente_recebeu    MY_CPF                                NOT NULL,

  CONSTRAINT Documento_PK PRIMARY KEY(id_documento),
  CONSTRAINT Documento_FK_Participante FOREIGN KEY(id_participante) REFERENCES Participante(id_adolescente),
  CONSTRAINT Documento_FK_Assistente_Recebeu FOREIGN KEY(cpf_assistente_recebeu) REFERENCES AssistenteSocial(cpf),
  CONSTRAINT Documento_CHK_data_entrega_consistent CHECK(data_entrega <= current_date)
);
CREATE INDEX Documento_INX_FK_Participante
  ON Documento(id_participante);

CREATE INDEX Documento_INX_FK_Assistente
  ON Documento(cpf_assistente_recebeu);
-- Constraints
-- id_documento: PK
-- id_participante: FK => Participante
-- cpf_assistente_recebeu: FK => Assistente
-- cpf_assistente_recebeu: <constraints MY_CPF>
-- data_entrega: <= today

DROP TABLE IF EXISTS Orcamento;
CREATE TABLE Orcamento(
  id_adolescente               INTEGER                            NOT NULL,
  data_que_foi_realizada       TIMESTAMP WITH TIME ZONE           NOT NULL,
  observacoes                  TEXT                               NOT NULL,
  data_da_proxima_entrevista   TIMESTAMP WITH TIME ZONE           NOT NULL,
  id_documento_comprovante     INTEGER                   UNIQUE   NOT NULL,
  mes                          NUMERIC(2,0)                       NOT NULL,  -- Máximo de 2 digitos sem casa decimal

  CONSTRAINT Orcamento_PK PRIMARY KEY(id_adolescente, data_que_foi_realizada),
  CONSTRAINT Orcamento_FK_Adolescente FOREIGN KEY(id_adolescente) REFERENCES Adolescente(id_adolescente),
  CONSTRAINT Orcamento_FK_Documento FOREIGN KEY(id_documento_comprovante) REFERENCES Documento(id_documento),
  CONSTRAINT Orcamento_CHK_data_realizada_consistent CHECK(data_que_foi_realizada < current_timestamp),
  CONSTRAINT Orcamento_CHK_data_proxima_entrevista CHECK(data_da_proxima_entrevista > current_timestamp),
  CONSTRAINT Orcamento_CHK_mes_range CHECK(numrange(1, 12) @> mes)
);
CREATE INDEX Orcamento_INX_FK_Adolescente
  ON Orcamento(id_adolescente);

CREATE INDEX Orcamento_INX_FK_Documento
  ON Orcamento(id_documento_comprovante);
-- Constraints
-- id_adolescente, data_que_foi_realizada: PK composta
-- id_adolescente: FK => Adolescente
-- data_que_foi_realizada: < now
-- data_da_proxima_entrevista: > now
-- id_documento_comprovante: FK => Documento
-- mes: 1-12

DROP TABLE IF EXISTS Desliga;
CREATE TABLE Desliga(
  id_bolsista          INTEGER                                    NOT NULL,
  cpf_assistente       MY_CPF                                     NOT NULL,
  data                 TIMESTAMP WITH TIME ZONE                   NOT NULL,
  motivo_predefinido   CHAR(40)                                   NOT NULL,
  motivo_outro         VARCHAR(255)                                   NULL,

  CONSTRAINT Desliga_PK PRIMARY KEY(id_bolsista),
  CONSTRAINT Desliga_FK_Bolsista FOREIGN KEY(id_bolsista) REFERENCES Bolsista(id_adolescente),
  CONSTRAINT Desliga_FK_Assistente FOREIGN KEY(cpf_assistente) REFERENCES AssistenteSocial(cpf),
  CONSTRAINT Desliga_CHK_motivo_options CHECK(motivo_predefinido IN ('Abandono do curso', 'Conclusão de curso', 'Descumprimento das normas institucionais', 'Mudaça na situação socio-econômica', 'Ausência', 'Reprovação', 'Se mudar para fora do estado', 'Falecimento', 'Outro')),
  CONSTRAINT Desliga_CHK_motivo_outro CHECK(
    (motivo_predefinido = 'Outro' AND motivo_outro is not NULL)
    OR (motivo_predefinido != 'Outro' AND motivo_outro is NULL)
  ),
  CONSTRAINT Desliga_CHK_data_consistent CHECK(data < current_timestamp)
);
CREATE INDEX Desliga_INX_FK_Assistente
  ON Desliga(cpf_assistente);

-- Constraints
-- id_bolsista: PK
-- id_bolsista: FK => Bolsista
-- cpf_assistente: FK => Assistente
-- cpf_asissitente: <constraints de MY_CPF>
-- motivo_pre_definido: ['Abandono do curso', 'Conclusão de curso', 'Descumprimento das normas institucionais', 'Mudaça na situação socio-econômica', 'Ausência', 'Reprovação', 'Se mudar para fora do estado', 'Falecimento', 'Outro']
-- motivo_predefinido == 'Outro' <=> E motivo_outro
-- data: < now

DROP TABLE IF EXISTS Reprova;
CREATE TABLE Reprova(
  id_candidato         INTEGER                                    NOT NULL,
  cpf_assistente       MY_CPF                                     NOT NULL,
  data                 TIMESTAMP WITH TIME ZONE                   NOT NULL,
  motivo_predefinido   CHAR(37)                                   NOT NULL,
  motivo_outro         VARCHAR(255)                                   NULL,

  CONSTRAINT Reprova_PK PRIMARY KEY(id_candidato),
  CONSTRAINT Reprova_FK_Candidato FOREIGN KEY(id_candidato) REFERENCES Candidato(id_adolescente),
  CONSTRAINT Reprova_FK_Assistente FOREIGN KEY(cpf_assistente) REFERENCES AssistenteSocial(cpf),
  CONSTRAINT Reprova_CHK_data_consistent CHECK(data < current_timestamp),
  CONSTRAINT Reprova_CHK_motivo_options CHECK(motivo_predefinido in ('Idade inferior à permitida.', 'Idade superior à permitida', 'Final de curso', 'Baixo rendimento', 'Estuda em colégio público', 'Situação sócio-econômica incompatível', 'Falecimento', 'Outro')),
  CONSTRAINT Reprova_CHK_motivo_outro CHECK(
    (motivo_predefinido = 'Outro' AND motivo_outro is not NULL)
    OR (motivo_predefinido != 'Outro' AND motivo_outro is NULL)
  )
);
CREATE INDEX Reprova_INX_FK_Assistente
  ON Reprova(cpf_assistente);
-- Constraints
-- id_candidato: PK
-- id_candidato: FK => Candidato
-- cpf_assistente: FK => Assistente
-- cpf_assistente: <constraints MY_CPF>
-- data: < now
-- motivo_pre_definido: ['Idade inferior à permitida.', 'Idade superior à permitida', 'Final de curso', 'Baixo rendimento', 'Estuda em colégio público', 'Situação sócio-econômica incompatível', 'Falecimento', 'Outro']
-- motivo_predefinido == 'Outro' <=> E motivo_outro

DROP TABLE IF EXISTS EnviaCarta;
CREATE TABLE EnviaCarta(
  id_envia_carta   SERIAL                NOT NULL,
  id_adolescente   INTEGER               NOT NULL,
  cpf_assistente   MY_CPF                NOT NULL,
  data             DATE                  NOT NULL,

  CONSTRAINT EnviaCarta_PK PRIMARY KEY(id_envia_carta),
  CONSTRAINT EnviaCarta_FK_Adolescente FOREIGN KEY(id_adolescente) REFERENCES Adolescente(id_adolescente),
  CONSTRAINT EnviaCarta_FK_Assistente FOREIGN KEY(cpf_assistente) REFERENCES AssistenteSocial(cpf),
  CONSTRAINT EnviaCarta_CHK_data_consistent CHECK(data < current_date)
);
CREATE INDEX EnviaCarta_INX_FK_Adolescente
  ON EnviaCarta(id_adolescente);

CREATE INDEX EnviaCarta_INX_FK_Assistente
  ON EnviaCarta(cpf_assistente);
-- Constraints
-- id_envia_carta: PK
-- id_adolescente: FK => Adolescente
-- cpf_assistente: FK => Assistente
-- cpf_assistente: <constraints MY_CPF>
-- data: < now

DROP TABLE IF EXISTS TelefoneFixo;
CREATE TABLE TelefoneFixo(
  id_adolescente  INTEGER                                         NOT NULL,
  numero          char(13)                                        NOT NULL,
  responsavel     VARCHAR(255)                                    NOT NULL,

  CONSTRAINT TelefoneFixo_PK PRIMARY KEY(id_adolescente, numero),
  CONSTRAINT TelefoneFixo_FK_Adolescente FOREIGN KEY(id_adolescente) REFERENCES Adolescente(id_adolescente),
  CONSTRAINT TelefoneFixo_CHK_numero_format CHECK(numero ~ '^\(\d{2}\)\d{4}-\d{4}$')
);
CREATE INDEX TelefoneFixo_INX_FK_Adolescente
  ON TelefoneFixo(id_adolescente);
-- Constraints
-- id_adolescente, numero: PK composta
-- numero: \(\d{2}\)\d{4}-\d{4}

DROP TABLE IF EXISTS Celular;
CREATE TABLE Celular(
  id_adolescente  INTEGER                                         NOT NULL,
  numero          CHAR(14)                                        NOT NULL,
  responsavel     VARCHAR(255)                                    NOT NULL,

  CONSTRAINT Celular_PK PRIMARY KEY(id_adolescente, numero),
  CONSTRAINT Celular_FK_Adolescente FOREIGN KEY(id_adolescente) REFERENCES Adolescente(id_adolescente),
  CONSTRAINT Celular_CHK_numero_format CHECK(numero ~ '^\(\d{2}\)(9\d{4}-\d{4}|\d{4}-\d{4} )$')
);
CREATE INDEX Celular_INX_FK_Adolescente
  ON Celular(id_adolescente);

-- Constraints
-- id_adolescente, numero: PK composta
-- numero: \(\d{2}\)(9|)\d{4}-\d{4}

DROP TABLE IF EXISTS Despesa;
CREATE TABLE Despesa(
  id_despesa        SERIAL                                        NOT NULL,
  id_participante   INTEGER                                       NOT NULL,
  tipo              CHAR(31)                                      NOT NULL,
  descricao         TEXT                                          NOT NULL,
  valor             NUMERIC(5, 2)                                 NOT NULL, -- Max de 5 digitos mais 2 casas decimais: 99999.99                                        

  CONSTRAINT Despesa_PK PRIMARY KEY(id_despesa),
  CONSTRAINT Despesa_FK_Participante FOREIGN KEY(id_participante) REFERENCES Participante(id_adolescente),
  CONSTRAINT Despesa_CHK_tipo_options CHECK(tipo IN ('Água', 'Luz', 'Gás', 'Telefone', 'Internet', 'TV à cabo', 'Plano de saúde', 'Mensalidade escolar ou de curso'))
);
CREATE INDEX Despesa_INX_FK_Participante
  ON Despesa(id_participante);
-- Constraints
-- id_despesa: PK
-- id_participante: FK => Participante
-- tipo: ['Água', 'Luz', 'Gás', 'Telefone', 'Internet', 'TV à cabo', 'Plano de saúde', 'Mensalidade escolar ou de curso']

DROP TABLE IF EXISTS TipoTransporte;
CREATE TABLE TipoTransporte(
  id_participante   INTEGER                                NOT NULL,
  tipo              CHAR(9)                                NOT NULL,
  descricao_outro   TEXT                                       NULL,

  CONSTRAINT TipoTransporte_PK PRIMARY KEY(id_participante, tipo),
  CONSTRAINT TipoTransporte_FK_Participante FOREIGN KEY(id_participante) REFERENCES Participante(id_adolescente),
  CONSTRAINT TipoTransporte_CHK_tipo_options CHECK(tipo IN ('Ônibus', 'Trem', 'Metrô', 'Carro', 'Bicicleta', 'Andando', 'Outro')),
  CONSTRAINT TipoTransporte_CHK_tipo_descricao_outro CHECK(
    (tipo = 'Outro' AND descricao_outro is not NULL)
    OR (tipo != 'Outro' AND descricao_outro is NULL)
  )
);
CREATE INDEX TipoTransporte_INX_FK_Particiante
  ON TipoTransporte(id_participante);
-- Constraints
-- id_participante, tipo: PK composta
-- tipo: ['Ônibus', 'Trem', 'Metrô', 'Carro', 'Bicicleta', 'Andando' e 'Outro']
-- tipo == 'Outro' <=> E descricao_outro


DROP TABLE IF EXISTS ArtificialIds;
CREATE TABLE ArtificialIds(
  table_name      VARCHAR(100) NOT NULL,
  next_id         INTEGER      NOT NULL DEFAULT 0,

  CONSTRAINT ArtificialIds_PK PRIMARY KEY(table_name)
);

DROP FUNCTION IF EXISTS nextArtificialId(table_name_param TEXT);
CREATE FUNCTION nextArtificialId(table_name_param TEXT) RETURNS INTEGER AS $$
DECLARE
  artificial_id ArtificialIds;
BEGIN
  select * from ArtificialIds ai where ai.table_name = table_name_param into artificial_id;

  IF artificial_id is NULL THEN
    insert into artificialIds values(table_name_param, 1);
    RETURN 0;
  ELSE
    update artificialIds set next_id = next_id + 1 where table_name = table_name_param;
    RETURN artificial_id.next_id;
  END IF;
END;
$$ LANGUAGE plpgsql;


-- Assistente default, poderá ser removida após a implemenação do cadastro de assistentes
insert into assistentesocial values('871.634.974-15', 'Assistente Admin', '1983-03-22', 'assistente@email.com', '12341234');